﻿--获取网站列表
CREATE proc [dbo].[proc_SiteForm_GetList]
(
	@startIndex int,
	@endIndex int,
	@docount bit,
	@username nvarchar(50),
	@productId int,
	@stateId int,
	@customer nvarchar(100)
) 
as
	--公司Id
	declare @companyid varchar (4)
	set @companyid = (select companyid from aspnet_Users where username=@username)
	
	set nocount on
		if(@docount=1)
		begin
			select sf.id,sf.CustomerId,sf.CustomerName,sf.OrderId,sf.ProductId,'' as ProductName,sf.ContactPerson,sf.ContactTel,sf.WebSitePrice,sf.SignDate,sf.StateId,'' as StateName,sf.Description,sf.CreateTime from siteform sf
			where sf.companyid = @companyid and sf.productId = @productId and (case @stateId when 0 then 0 else sf.stateId end) = @stateId and sf.CustomerName like '%'+@customer+'%'
		
		end
		else
		begin
		declare @indextable table(id int identity(1,1),nid int)
		set rowcount @endIndex
		insert into @indextable(nid) select sf.id from siteform sf where sf.companyid = @companyid and sf.productId = @productId and (case @stateId when 0 then 0 else sf.stateId end) = @stateId and sf.CustomerName like '%'+@customer+'%'
		select 
			sf.id,
			sf.OrderId,
			sf.ProductId,
			(select title from product p where sf.ProductId=p.Id) as ProductName,
			sf.CustomerId,
			sf.CustomerName,
			sf.ContactPerson,
			sf.ContactTel,
			sf.SignDate,
			sf.WebSitePrice,
			sf.Description,
			sf.CreateTime,
			sf.StateId,
			(select ss.title from ProjectState ss where ss.ProjectId=sf.stateid) as StateName
		from siteform sf inner join @indextable t on sf.id=t.nid
		where 
			t.id between @startIndex and @endIndex 
		order by t.id
		end
	set nocount off

